package com.atguigu.gmall.realtime.util;

import com.alibaba.fastjson.JSONObject;
import com.atguigu.gmall.realtime.common.Constant;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.flink.shaded.guava18.com.google.common.base.CaseFormat;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * @Author lzc
 * @Date 2022/12/27 10:20
 */
public class JdbcUtil {
    
    public static Connection getPhoenixConnection() {
        
        String driver = Constant.PHOENIX_DRIVER;
        String url = Constant.PHOENIX_URL;
        
        return getJdbcConnection(driver, url, null, null);
    }
    
    private static Connection getJdbcConnection(String driver,
                                                String url,
                                                String user,
                                                String password) {
        // 需要先加载驱动
        try {
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        }
        
        try {
            return DriverManager.getConnection(url, user, password);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        
    }
    
    public static void closeConnection(Connection conn) {
        if (conn != null) {
            try {
                conn.close();
            } catch (SQLException e) {
                throw new RuntimeException(e);
            }
            
        }
    }
    
    public static Connection getMysqlConnection() {
        String driver = Constant.MYSQL_DRIVER;
        String url = Constant.MYSQL_URL;
        String user = "root";
        String password = "aaaaaa";
        return getJdbcConnection(driver, url, user, password);
    }
    // 通用的查询方法:
    // 肯定会创建泛型对象, 则一定用到反射, 所以必须传入一个 Class
    public static <T> List<T> queryList(Connection conn,
                                        String querySql,
                                        Object[] arg,
                                        Class<T> tClass,
                                        boolean isUnderLineToCamel) throws Exception {
        ArrayList<T> result = new ArrayList<>();
        // 1. 根据链接对象和 sql 语句得到一个预处理语句
        PreparedStatement ps = conn.prepareStatement(querySql);
        // 2. 给 sql 中的占位符进行赋值
        if (arg != null) {
            for (int i = 0; i < arg.length; i++) {
                Object v = arg[i];
                ps.setObject(i + 1, v);
            }
        }
        // 3. 执行查询语句
        ResultSet resultSet = ps.executeQuery();
        ResultSetMetaData metaData = resultSet.getMetaData();
        // 4. 解析查到的结果, 封装到一个 List 集合中
        while (resultSet.next()) {
            // 读取到一行数据: 每一行封装到一个 T 类型的对象中
            // 创建 T 类型的对象
            T t = tClass.newInstance();  // new
            // 读取这一行的每一列, 放在到对象 t 中
            // 遍历所有列
            for (int i = 1; i <= metaData.getColumnCount(); i++) {
                // 获取列名
                String columnName = metaData.getColumnLabel(i);
                if (isUnderLineToCamel) {
                    // 需要把列名转成下划线
                    // aa_bb => aaBb   AA_BB
                    // aaBb  AaBb
                     columnName = CaseFormat.LOWER_UNDERSCORE.to(CaseFormat.LOWER_CAMEL, columnName);
                }
                
                // 获取第 i 列的值
                Object v = resultSet.getObject(i);
                // 给 t 对象的属性进行赋值
                BeanUtils.setProperty(t, columnName, v);// t.columnName = v
            }
    
            result.add(t);
            
        }
        
        return result;
    }
    
    public static void main(String[] args) throws Exception {
        /*List<TableProcess> list = queryList(getMysqlConnection(),
                                            "select * from table_process",
                                            null,
                                            TableProcess.class,
                                            true);
        for (TableProcess tp : list) {
            System.out.println(tp);
        }*/
        List<JSONObject> list = queryList(getMysqlConnection(),
                                                 "select * from gmall2022.user_info where id=?",
                                                 new Object[]{10},
                                                 JSONObject.class,
                                                 true
        );
        for (JSONObject obj : list) {
            System.out.println(obj);
        }
    }
}
